# -*- coding: utf-8 -*-

# Define your item pipelines here
#
# Don't forget to add your pipeline to the ITEM_PIPELINES setting
# See: http://doc.scrapy.org/en/latest/topics/item-pipeline.html

from pymysql import cursors
import urllib
import time
import os
import datetime
# 使用twsited异步IO框架，实现数据的异步写入。
from twisted.enterprise import adbapi
from iQiYiSpider.util import cityUtil
from iQiYiSpider.items import IqiyispiderItem, BaiDuSpiderItem, IFengSpiderItem, QiDianSpiderItem, baiDuAppAdsItem, baiDuMobileItem, dayDayNewsAppItem
class IqiyispiderPipeline(object):
    def __init__(self, dbpool):
        self.dbpool = dbpool
    @classmethod
    def from_settings(cls, settings):
        params = dict(
            host=settings['MYSQL_HOST'],
            db=settings['MYSQL_DB'],
            user=settings['MYSQL_USER'],
            password=settings['MYSQL_PASSWD'],
            charset=settings['MYSQL_CHARSET'],
            port=settings['MYSQL_PORT'],
            cursorclass=cursors.DictCursor,
        )
        # 初始化数据库连接池(线程池)
        # 参数一：mysql的驱动
        # 参数二：连接mysql的配置信息
        dbpool = adbapi.ConnectionPool('pymysql', **params)
        return cls(dbpool)

    def process_item(self, item, spider):
        if isinstance(item, IqiyispiderItem):
            query = self.dbpool.runInteraction(self.insert_db, item)
            query.addErrback(self.handle_error)
            return item
        elif isinstance(item, BaiDuSpiderItem):
            query = self.dbpool.runInteraction(self.baidu_insert_db, item)
            query.addErrback(self.handle_error)
            return item
        elif isinstance(item, IFengSpiderItem):
            query = self.dbpool.runInteraction(self.ifeng_insert_db, item)
            query.addErrback(self.handle_error)
            return item

        elif isinstance(item, QiDianSpiderItem):
            query = self.dbpool.runInteraction(self.qidian_insert_db, item)
            query.addErrback(self.handle_error)
            return item
        elif isinstance(item, baiDuAppAdsItem):
            query = self.dbpool.runInteraction(self.baidu_app_ads_insert_db, item)
            query.addErrback(self.handle_error)
            return item
        elif isinstance(item, baiDuMobileItem):
            query = self.dbpool.runInteraction(self.baidu_mobile_sem_insert, item)
            query.addErrback(self.handle_error)
            return item
        elif isinstance(item, dayDayNewsAppItem):
            query = self.dbpool.runInteraction(self.dayday_news_insert, item)
            query.addErrback(self.handle_error)
            return item

    def handle_error(self, failure):
        print('-------数据库写入失败:', failure)

    #创意流广告插入数据库
    def insert_db(self, cursor, item):
        insert_sql = "INSERT INTO ad_info(ad_id, ad_title, ad_hot, ad_logo, ad_img, ad_industry, ad_time, ad_landing_page_url) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
        cursor.execute(insert_sql, (item['ad_id'], item['ad_title'], item['ad_hot'], item['ad_logo'], item['ad_img'], item['ad_industry'], str(item['ad_time']), item['ad_landing_page_url']))

    #百度pc端广告插入数据库
    def baidu_insert_db(self, cursor, item):

        baidu_query_flag = self.baidu_query_info(cursor, item)
        list_city = cityUtil()
        city_flag = False
        if item['company_name']:
            for city in list_city:
                if city in item['company_name']:
                    city_flag = True
                    item['city'] = city
                    break
            if city_flag:
                if baidu_query_flag is None and item['company_name'] is not None:
                    try:
                        print('--------------执行插入百度广告---------------', item['company_name'])
                        baidu_insert_sql = "INSERT INTO baidu_ad_info(company_name, company_url, company_title, company_date, company_type, company_bussines, company_record, city) VALUES (%s, %s, %s, %s, %s, %s, %s, %s)"
                        cursor.execute(baidu_insert_sql, (item['company_name'], item['company_url'], item['company_title'], item['company_date'], item['company_type'], item['company_bussines'], item['company_record'], item['city']))
                    except Exception as e:
                        print('-------------------插入百度广告失败-----------', e)
                elif baidu_query_flag and item['company_name'] is not None:
                    id = str(baidu_query_flag['id'])
                    baidu_update_sql = "UPDATE baidu_ad_info SET company_name = %s, company_url = %s, company_title = %s, company_date = %s, company_type = %s, company_bussines = %s, company_record = %s, city = %s where id = %s"
                    cursor.execute(baidu_update_sql, (item['company_name'], item['company_url'], item['company_title'], item['company_date'], item['company_type'], item['company_bussines'], item['company_record'], item['city'], id))

    def baidu_query_info(self, cursor, item):
        baidu_query_data = {'company_name': item['company_name']}
        baidu_query_sql = "SELECT * FROM baidu_ad_info WHERE company_name = %(company_name)s"
        cursor.execute(baidu_query_sql, baidu_query_data)
        results = cursor.fetchall()
        if len(results) > 0:
            print('---------百度广告已存在-----------------', item['company_name'])
            return results[0]
        else:
            return None

    #凤凰网pc端广告插入数据库
    def ifeng_insert_db(self, cursor, item):
        ifeng_query_flag = self.ifeng_query_info(cursor, item)
        if ifeng_query_flag is False:
            try:
                print('------------------执行插入凤凰广告-----------', item)
                ifeng_insert_sql = "INSERT INTO ifeng_ad_info(ad_real_url, ad_url, ad_img) VALUES(%s, %s, %s)"
                cursor.execute(ifeng_insert_sql, (item['ad_real_url'], item['ad_url'], item['ad_img']))
            except Exception as e:
                print('-------------------插入凤凰广告失败-----------', e)
    def ifeng_query_info(self, cursor, item):
        ifeng_qeury_data = {'ad_real_url': item['ad_real_url']}
        ifeng_qeury_sql = "SELECT * FROM ifeng_ad_info WHERE ad_real_url = %(ad_real_url)s"
        cursor.execute(ifeng_qeury_sql, ifeng_qeury_data)
        results = cursor.fetchall()
        if len(results) > 0:
            print('--------------凤凰广告已存在----------------------', item)
            return True
        else:
            return False

    #起点小说插入数据库
    def qidian_insert_db(self, cursor, item):
        qidian_query_flag = self.qidian_query_info(cursor, item)
        if qidian_query_flag is False:
            try:
                print('------------------执行插入起点小说-----------', item)
                qidian_insert_sql = "INSERT INTO qidian_note_info(note_name, note_url, note_img, note_type, note_author, note_abstract, note_status, note_word_num) VALUES(%s, %s, %s, %s, %s, %s, %s, %s)"
                cursor.execute(qidian_insert_sql, (item['note_name'], item['note_url'], item['note_img'], item['note_type'], item['note_author'], item['note_abstract'], item['note_status'], item['note_word_num']))
            except Exception as e:
                print('-------------------插入起点小说失败-----------', e)
    def qidian_query_info(self, cursor, item):
        qidian_qeury_data = {'note_name': item['note_name']}
        qidian_qeury_sql = "SELECT * FROM qidian_note_info WHERE note_name = %(note_name)s"
        cursor.execute(qidian_qeury_sql, qidian_qeury_data)
        results = cursor.fetchall()
        if len(results) > 0:
            print('--------------起点小说已存在----------------------', item)
            return True
        else:
            return False


    def baidu_app_ads_insert_db(self, cursor, item):
        image_path = '/repo/smspider/images/baidunews_app/'
        image_url = item['image']
        title = item['title']
        if title:
            query_flag = self.baidu_app_ads_query_info(cursor, item)
            if query_flag is None:
                if image_url:
                    try:
                        image_list = []
                        for i_u in range(len(image_url)):
                            image_new_name = item['ad_id'] + '_' + str(i_u) + '.jpg'
                            urllib.request.urlretrieve(image_url[i_u], image_path + image_new_name)
                            image_list.append(image_new_name)
                        insert_sql = "INSERT INTO baidu_app_ads(ad_id, sub_tag, mode, title, tag, title_tts, prefetch_image, expire, feed_floor_type, tag_has_border, tag_color, tag_skin_color, tag_border_color, tag_skin_border_color, tag_text_size, source, duration, image, type, comment_num, video, extra_data, auto_play_switch_ad, videoInfo, prefetch_video, cmd, lp_cmd, extra_info, ad_append_info, ad_url, local_img, create_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                        cursor.execute(insert_sql, (item['ad_id'], item['sub_tag'], item['mode'], item['title'], item['tag'], item['title_tts'], item['prefetch_image'], item['expire'], item['feed_floor_type'], item['tag_has_border'], item['tag_color'], item['tag_skin_color'], item['tag_border_color'], item['tag_skin_border_color'], item['tag_text_size'], item['source'], item['duration'], str(item['image']), item['type'], item['comment_num'], item['video'], item['extra_data'], item['auto_play_switch_ad'], item['videoInfo'], item['prefetch_video'], item['cmd'], item['lp_cmd'], item['extra_info'], item['ad_append_info'], item['ad_url'], str(image_list), str(datetime.datetime.now())))
                    except Exception as e:
                        print(e)
                else:
                    insert_sql = "INSERT INTO baidu_app_ads(ad_id, sub_tag, mode, title, tag, title_tts, prefetch_image, expire, feed_floor_type, tag_has_border, tag_color, tag_skin_color, tag_border_color, tag_skin_border_color, tag_text_size, source, duration, image, type, comment_num, video, extra_data, auto_play_switch_ad, videoInfo, prefetch_video, cmd, lp_cmd, extra_info, ad_append_info, ad_url, create_time) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s)"
                    cursor.execute(insert_sql, (item['ad_id'], item['sub_tag'], item['mode'], item['title'], item['tag'], item['title_tts'], item['prefetch_image'], item['expire'], item['feed_floor_type'], item['tag_has_border'], item['tag_color'], item['tag_skin_color'], item['tag_border_color'], item['tag_skin_border_color'], item['tag_text_size'], item['source'], item['duration'], str(item['image']), item['type'], item['comment_num'], item['video'], item['extra_data'], item['auto_play_switch_ad'], item['videoInfo'], item['prefetch_video'], item['cmd'], item['lp_cmd'], item['extra_info'], item['ad_append_info'], item['ad_url'], str(datetime.datetime.now())))
            else:
                # print(query_flag[0])
                if image_url:
                    try:
                        image_list = []
                        for i_u in range(len(image_url)):
                            image_new_name = item['ad_id'] + '_' + str(i_u) + '.jpg'
                            urllib.request.urlretrieve(image_url[i_u], image_path + image_new_name)
                            image_list.append(image_new_name)
                        updata_sql = "UPDATE baidu_app_ads SET ad_url = %s, local_img = %s, image = %s, update_time = %s, discover_count = discover_count + 1 WHERE title = %s"
                        cursor.execute(updata_sql, (item['ad_url'], str(image_list), str(image_url), str(datetime.datetime.now()), item['title']))
                    except Exception as e:
                        print(e)
                else:
                    updata_sql = "UPDATE baidu_app_ads SET ad_url = %s, update_time = %s, discover_count = discover_count + 1 WHERE title = %s"
                    cursor.execute(updata_sql, (item['ad_url'], str(datetime.datetime.now()), item['title']))

    def baidu_app_ads_query_info(self, cursor, item):
        query_data = {'title': item['title'], 'source': item['source']}
        query_sql = "SELECT * FROM baidu_app_ads WHERE title = %(title)s and source = %(source)s"
        cursor.execute(query_sql, query_data)
        results = cursor.fetchall()
        if len(results) > 0:
            return results
        else:
            return None

    def baidu_mobile_sem_insert(self, cursor, item):
        query_data = {'company_name': item['company_name']}
        query_sql = "SELECT * FROM baidu_mobile_sem WHERE company_name = %(company_name)s limit 1"
        cursor.execute(query_sql, query_data)
        result = cursor.fetchall()
        list_city = cityUtil()
        city_flag = False
        for city in list_city:
            if city in item['company_name']:
                city_flag = True
                item['city'] = city
                break
        if city_flag:
            if result:
                update_sql = "UPDATE baidu_mobile_sem SET company_url = %s, city = %s WHERE company_name = %s"
                cursor.execute(update_sql, (item['company_url'], item['city'], item['company_name']))
            else:
                insert_sql = "INSERT INTO baidu_mobile_sem(company_name, company_domain, company_url, company_title, city, create_time) VALUES (%s, %s, %s, %s, %s, %s)"
                cursor.execute(insert_sql, (item['company_name'], item['company_domain'], item['company_url'], item['company_title'], item['city'], str(datetime.datetime.now())))

    def dayday_news_insert(self, cursor, item):
        image_path = '/repo/smspider/images/daydaynews_app/'
        image_url = item['thumbnails_qqnews']
        title = item['title']
        query_data = {'title': title}
        query_sql = "select * from daydaynews_app where title = %(title)s limit 1"
        cursor.connection.ping()
        cursor.execute(query_sql, query_data)
        result = cursor.fetchall()
        if result:
            if image_url:
                    try:
                        image_list = []
                        for i_u in range(len(image_url)):
                            image_new_name = item['ad_id'] + '_' + str(i_u) + '.jpg'
                            urllib.request.urlretrieve(image_url[i_u], image_path + image_new_name)
                            image_list.append(image_new_name)
                        update_sql = "update daydaynews_app set short_url = %s, thumbnails_qqnews = %s, local_img = %s, update_time = %s, discover_count = discover_count + 1 where title = %s"
                        cursor.connection.ping()
                        cursor.execute(update_sql, (item['short_url'], item['thumbnails_qqnews'], str(image_list), str(datetime.datetime.now()), item['title']))
                    except Exception as e:
                        print(e)

        else:
            if image_url:
                try:
                    image_list = []
                    for i_u in range(len(image_url)):
                        image_new_name = item['ad_id'] + '_' + str(i_u) + '.jpg'
                        urllib.request.urlretrieve(image_url[i_u], image_path + image_new_name)
                        image_list.append(image_new_name)
                    insert_sql = "INSERT INTO daydaynews_app(ad_id, title, articletype, timestamp, time, commentid, picShowType, srcName, abstract, linkUrl, commentShareTitle, mmaExposureLink, mmaClickLink, mmaReportMethod, short_url, url, seq_no, pvid, thumbnails_qqnews, alg_version, local_img, create_time, discover_count) VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s, %s);"
                    cursor.connection.ping()
                    cursor.execute(insert_sql, (item['ad_id'], item['title'], str(item['articletype']), str(item['timestamp']), str(item['time']), str(item['commentid']), str(item['picShowType']), item['srcName'], item['abstract'], item['linkUrl'], item['commentShareTitle'], item['mmaExposureLink'], item['mmaClickLink'], str(item['mmaReportMethod']), item['short_url'], item['url'], str(item['seq_no']), str(item['pvid']), item['thumbnails_qqnews'], item['alg_version'], str(image_list), str(datetime.datetime.now()), str(1)))
                except Exception as e:
                    print(e)